package com.cq.xjms.service.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.extremecomponents.table.limit.Limit;

import com.cq.xjms.model.Score;
import com.cq.xjms.model.Stat;
import com.cq.xjms.service.StatManager;

public class StatManagerImpl extends BaseManagerImpl<Score> implements StatManager{

	private static Logger LOG = Logger.getLogger(StatManagerImpl.class);
	public int countCourseRank(Long departmentId, Long clazzId) {
		int count = 0;
		StringBuffer hql = new StringBuffer();
		hql.append("select count(s.*),sum(s.CONPOINT) as conpoint,s.student_ID as size from ");
		hql.append("score s left join student t on s.student_id = t.id ");
		hql.append(" left join clazz c on t.clazz_id = c.id ");
		if(clazzId!=null){
			hql.append("  and c.id="+clazzId);
		}
		hql.append(" left join department d on c.department_id = d.id");
		if(departmentId!=null){
			hql.append(" and d.id="+departmentId) ;
		}
		hql.append(" group by s.student_ID ");
		try{
			Query query = this.nativeQuery(hql.toString());
			Object result = query.getSingleResult();
			BigInteger bint = null;
			if(result!=null){
				bint = (BigInteger)result;
				count = bint.intValue();
			}
//			if(result!=null && result.size()>0){
//				return Integer.valueOf(result.get(0).toString());
//			}
		}catch(Exception e){
			LOG.error("count student failed!", e);
		}
		return count;
	}

	

	@Override
	public List<Stat> courseRank(Long departmentId, Long clazzId, Limit limit) {
		StringBuffer hql = new StringBuffer();
		hql.append("select sum(s.CONPOINT) as conpoint,s.student_ID,t.name,t.stuNo,c.name clazzName,d.name dName,o.term from score s,student t,clazz c,department d,course o "); 
		hql.append(" where s.student_id = t.id and t.clazz_id = c.id and c.department_id = d.id and s.course_ID = o.id ");
		if(clazzId!=null){
			hql.append("  and c.id="+clazzId);
		}
		if(clazzId == null && departmentId!=null){
			hql.append(" and d.id="+departmentId) ;
		}
		hql.append(" group by s.student_ID order by conpoint desc ");
		List<Stat> scores = new ArrayList<Stat>();
		try{
			Query query = this.nativeQuery(hql.toString());
			if (limit != null) {
				query.setFirstResult((limit.getPage() - 1)
						* limit.getCurrentRowsDisplayed());
				query.setMaxResults(limit.getCurrentRowsDisplayed());
			}
			List result = query.getResultList();
			for(int i=0;i<result.size();i++){
			   Object[] obj = (Object[])(result.get(i));	
			   double sumConpoint = Double.valueOf(obj[0].toString());
			   long stuId = Long.valueOf(obj[1].toString());
			   String stuName = obj[2].toString();
			   String stuNo = obj[3].toString();
			   String clazzName = obj[4].toString();
			   String deptName = obj[5].toString(); 
			   String term = obj[6].toString(); 
			   Stat s = new Stat(sumConpoint,stuNo,stuName,clazzName,deptName,stuId,term);
			   scores.add(s);
			}


		}catch(Exception e){
			LOG.error("query student failed!", e);
		}
		return scores;
		
	}



	@Override
	public int singleCountCourseRank(Long courseId) {
		// TODO Auto-generated method stub
		StringBuffer hql = new StringBuffer();
		hql.append("select count(c) from Score c  where 1=1");
		if(courseId !=null){
			hql.append(" and c.course.id=:courseId");
		}
		
		
		try{
			Query query = this.query(hql.toString());
			if(courseId !=null){
				query.setParameter("courseId", courseId);
			}
			
			
			Object result =  query.getSingleResult();
			return result == null ? 0
					: result instanceof Integer ? ((Integer) result).intValue()
							: ((Long) result).intValue();
		}catch(Exception e){
			LOG.error("count student failed!", e);
		}
		return 0;
	}



	@Override
	public List<Score> singleCourseRank(Long courseId, Limit limit) {
		// TODO Auto-generated method stub
		
		StringBuffer hql = new StringBuffer();
		hql.append("select c from Score c left join fetch c.student left join c.course where 1=1 ");
		
		if(courseId !=null){
			hql.append(" and c.course.id=:courseId");
		}
		hql.append(" order by c.conPoint desc");
		List<Score> scores = new ArrayList<Score>();
		try{
			Query query = this.query(hql.toString());
			if(courseId !=null){
				query.setParameter("courseId", courseId);
			}
			
			
			if (limit != null) {
				query.setFirstResult((limit.getPage() - 1)
						* limit.getCurrentRowsDisplayed());
				query.setMaxResults(limit.getCurrentRowsDisplayed());
			}
			scores =  query.getResultList();


		}catch(Exception e){
			LOG.error("query score failed!", e);
		}
		return scores;
		
	}



	@Override
	public int noPassCountRank(String yearterm) {
		// TODO Auto-generated method stub
		
		StringBuffer hql = new StringBuffer();
		hql.append("select count(c) from Score c  where c.point >0 and c.point <60");
		if(StringUtils.isNotEmpty(yearterm)){
			hql.append(" and c.course.term like:yearterm");
		}
		
		
		try{
			Query query = this.query(hql.toString());
			if(StringUtils.isNotEmpty(yearterm)){
				query.setParameter("yearterm", "%"+yearterm+"%");
			}
			
			
			Object result =  query.getSingleResult();
			return result == null ? 0
					: result instanceof Integer ? ((Integer) result).intValue()
							: ((Long) result).intValue();
		}catch(Exception e){
			LOG.error("count student failed!", e);
		}
		return 0;
	}



	@Override
	public List<Score> noPassRank(String yearterm, Limit limit) {
		// TODO Auto-generated method stub
		StringBuffer hql = new StringBuffer();
		hql.append("select c from Score c left join fetch c.student left join c.course where c.point>0 and c.point <60 ");
		
		if(StringUtils.isNotEmpty(yearterm)){
			hql.append(" and c.course.term like:yearterm");
		}
		hql.append(" order by c.createDate desc");
		List<Score> scores = new ArrayList<Score>();
		try{
			Query query = this.query(hql.toString());
			if(StringUtils.isNotEmpty(yearterm)){
				query.setParameter("yearterm", "%"+yearterm+"%");
			}
			
			
			if (limit != null) {
				query.setFirstResult((limit.getPage() - 1)
						* limit.getCurrentRowsDisplayed());
				query.setMaxResults(limit.getCurrentRowsDisplayed());
			}
			scores =  query.getResultList();


		}catch(Exception e){
			LOG.error("query score failed!", e);
		}
		return scores;
		
	}

	
	
}
